using DevExpress.CodeParser;
using DevExpress.Utils.Menu;
using DevExpress.XtraBars;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraGrid.Views.Grid.ViewInfo;
using DocumentFormat.OpenXml.Office2010.CustomUI;
using DocumentFormat.OpenXml.Presentation;
using Mysqlx.Crud;
using Shopfloormanagement;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.ComponentModel.Design.ObjectSelectorEditor;

namespace Shopfloormanagement
{
    public partial class CIPSFMView : DevExpress.XtraEditors.XtraForm
    {
        public static CIPSFMView instance = new CIPSFMView();
        public CIPSFMView()
        {
            instance = this;
            InitializeComponent();
            // 允许表单接收键盘事件
            this.KeyPreview = true;
        }
         public static DateTime selectdate = DateTime.Now;


        private void CIPSFMView_Load(object sender, EventArgs e)
        {
            
            ///右上角的月份选择，根据这个月份计算统计的KPI
            xtraTabControl1.ShowTabHeader = DevExpress.Utils.DefaultBoolean.False;
            // 设置日期编辑控件只显示年月
            currentMonth.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            currentMonth.Properties.DisplayFormat.FormatString = "yyyy-MM";
            currentMonth.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            currentMonth.Properties.EditFormat.FormatString = "yyyy-MM";
            currentMonth.Properties.Mask.EditMask = "yyyy-MM";
            // 设置日历弹出窗口只显示年月
            currentMonth.Properties.VistaCalendarViewStyle = DevExpress.XtraEditors.VistaCalendarViewStyle.YearView;
            currentMonth.Properties.CalendarView = DevExpress.XtraEditors.Repository.CalendarView.Vista;
            
            // 设置默认值为当前年月
            currentMonth.EditValue = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            
            // 初始化GridView
            InitGridView(selectdate);
            CalculateNewIL();
            //Tooltip
            toolTipController1.SetToolTip(lblPlannedSaving, "For SFM-L3 and below: The sum of the planned savings from the previous month for all projects in the current department.\nFor SFM-L4: The sum of the planned savings from the previous month for all projects in the plant.");
            toolTipController1.SetToolTip(lblActualSaving, "For SFM-L3 and below: The sum of the actual savings from the previous month for all projects in the current department.\nFor SFM-L4: The sum of the actual savings from the previous month for all projects in the plant.");
            toolTipController1.SetToolTip(lblRate, "Completion rate = Actual saving / Planned saving * 100%");
            toolTipController1.SetToolTip(lblOverdueRate, "Overdue rate = Overdue / Total * 100% = Overdue / ( Completed + Active + Overdue ) * 100%");
            toolTipController1.SetToolTip(lblADD, "New = Quantity of this month - Quantity of last month");
            toolTipController1.SetToolTip(labelControl2, "For SFM-L3 and below: All projects in the current department are counted by IL.\nFor SFM-L4: Projects at the plant's CIP Level 2 & 3 are counted by IL.");
            toolTipController1.SetToolTip(labelControl4, "For SFM-L3 and below: All projects in the current department are counted by IL.\nFor SFM-L4: Projects at the plant's CIP Level 2 & 3 are counted by IL.");
            toolTipController1.SetToolTip(lblCompleted, "After the project's savings calculation reaches 12 full months, the project will be counted as \"Completed\"");
            toolTipController1.SetToolTip(lblActive, "Active = Total - Completed - Overdue");
            toolTipController1.SetToolTip(lblOverDue, "If the \"Actual IL6 date\" is later than the \"IL6 due date\", the project will be counted as \"Overdue\"");
            toolTipController1.SetToolTip(lblActive, "Active = Total - Completed - Overdue");
            toolTipController1.SetToolTip(lblOverDue, "If the \"Actual IL6 date\" is later than the \"IL6 due date\", the project will be counted as \"Overdue\"");
            toolTipController1.SetToolTip(btnSummary, "Open an Excel workbook with the worksheets named \"Pivot table\",\"Summary\" and \"Overview\",\nbut without project detail saving calculation information");
            toolTipController1.SetToolTip(btnOverview, "Open an Excel workbook with the worksheets named \"Pivot table\",\"Summary\" and \"Overview\",\nand also project detail saving calculation information");

            //调试表格是否可见
            gridControlLastmonth.Visible = false;
            gridControlThismonth.Visible = false;


        }
        public void InitGridView(DateTime selectdate)
        {
            // 让所有数据单元格水平 & 垂直居中 \"\" 
            gridView1.Appearance.Row.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
            gridView1.Appearance.Row.TextOptions.VAlignment = DevExpress.Utils.VertAlignment.Center;

            // 让所有标题（列头） 水平 & 垂直居中
            gridView1.Appearance.HeaderPanel.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
            gridView1.Appearance.HeaderPanel.TextOptions.VAlignment = DevExpress.Utils.VertAlignment.Center;
            // 设置标题行自动换行
            gridView1.Appearance.HeaderPanel.TextOptions.WordWrap = DevExpress.Utils.WordWrap.Wrap;
            gridView1.Appearance.HeaderPanel.TextOptions.VAlignment = DevExpress.Utils.VertAlignment.Center;
            gridView1.OptionsView.ColumnHeaderAutoHeight = DevExpress.Utils.DefaultBoolean.True;
            //Total绿条
            panel14.BackColor = Color.FromArgb(146, 208, 80);



            // 创建一个 DateTime 变量，值为去年 1 月 1 日
            DateTime currentDate = selectdate;
            int lastYear = currentDate.Year - 1;
            DateTime lastYearJanuaryFirst = new DateTime(lastYear, 1, 1);
            //查询逻辑：A.工厂所有CIP L2、3   B.非ILX    C.上一年+今年所有项目
            string condition = $"delTag=0 and (IL != 'ILX' OR IL IS NULL)  order by PID";//Department筛选的是部门名称
            DataTable dt = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_list", condition);


            //查询汇总KPI用的savingdt
            DataTable dtCIP;
            if (SystemInfo.readLevel == publicFunc.MaxDepartmentLevel())

            {
                //先获取所有符合项目的清单,工厂所有
                DataTable dt1 = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_list", $"delTag=0 and (IL != 'ILX' OR IL IS NULL)  and createDate >='{lastYearJanuaryFirst}' order by PID");
                dtCIP = dt1;
            }
            else
            {
                //先获取所有符合项目的清单，本部门
                DataTable dt2 = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_list", $"delTag=0 and (IL != 'ILX' OR IL IS NULL)  and createDate >='{lastYearJanuaryFirst}'and DepartmentName = '{SystemInfo.readparentDepName}' order by PID");
                dtCIP = dt2;
            }
            if (dtCIP.Rows.Count>0)
            {
                string ids = String.Join(",", dtCIP.AsEnumerable().Select(row => row["ID"]));

                //只查今年的月份
                int thisYear = selectdate.Year;
                DataTable dtSavingBudget = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "CIP_saving", $"delTag=0 and savingType = 'Budget'and year ={thisYear} and CIPID IN ({ids})");
                DataTable dtSavingActual = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "CIP_saving", $"delTag=0 and savingType = 'Actual'and year ={thisYear} and CIPID IN ({ids})");



                decimal plannedSaving = 0;
                decimal actualSaving = 0;
                decimal rate = 0;
                //month是上个月
                int month = selectdate.Month - 1;
                //选择1月时计算上一年12月
                if (month == 0) 
                { 
                    //上一年12月
                    month = 12;
                    dtSavingBudget = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "CIP_saving", $"delTag=0 and savingType = 'Budget'and year ={thisYear-1} and CIPID IN ({ids})");
                    dtSavingActual = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "CIP_saving", $"delTag=0 and savingType = 'Actual'and year ={thisYear-1} and CIPID IN ({ids})");

                }
                string englishAbbreviation = System.Globalization.CultureInfo.GetCultureInfo("en-US")
                    .DateTimeFormat.GetAbbreviatedMonthName(month);
                if (dtSavingBudget.Rows.Count > 0)
                {
                    for (int i = 0; i < dtSavingBudget.Rows.Count; i++)
                    {
                        plannedSaving += Convert.ToDecimal(dtSavingBudget.Rows[i][$"{month}"]);
                        actualSaving += Convert.ToDecimal(dtSavingActual.Rows[i][$"{month}"]);
                    }
                }
                string strPlan = plannedSaving.ToString("0");
                string strActual = actualSaving.ToString("0");

                lblPlannedSaving.Text = $"{englishAbbreviation}" + ". planned saving:" + $"{strPlan}" + "K";
                lblActualSaving.Text = $"{englishAbbreviation}" + ". actual saving:" + $"{strActual}" + "K";
                if (plannedSaving > 0)
                {
                    rate = actualSaving / plannedSaving;
                }
                string strRate = rate.ToString("P0");
                lblRate.Text = $"{englishAbbreviation}" + ". saving completion rate:" + $"{strRate}";
            }


            //生成项目状态列
            dt.Columns.Add("Status", typeof(string));
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool isIL6CurrentDueDateNotNull = dt.Rows[i]["IL6CurrentDueDate"] != null && dt.Rows[i]["IL6CurrentDueDate"] != DBNull.Value;
                bool isIL6ActualDuedateNotNull = dt.Rows[i]["IL6ActualDuedate"] != null && dt.Rows[i]["IL6ActualDuedate"] != DBNull.Value;
                //1.先都默认为Active
                dt.Rows[i]["Status"] = "Active";

                //2.有计划开始日期，且没有实际开始日期，且计划开始日期早于今天的，视为Overdue
                if (isIL6CurrentDueDateNotNull&& !isIL6ActualDuedateNotNull)
                {
                    DateTime il6CurrentDueDate = (DateTime)dt.Rows[i]["IL6CurrentDueDate"];
                    //不可以精确到时间,只能精确到日
                    il6CurrentDueDate = il6CurrentDueDate.Date;

                    if (il6CurrentDueDate< DateTime.Now.Date)
                    {
                        dt.Rows[i]["Status"] = "Overdue";
                    }
                }
                //3.覆盖Completed，add或Edit页面手动设为completed的项目（算满12个月）
                if (dt.Rows[i]["savingCalculationStatus"].ToString() == "Completed")
                {
                    dt.Rows[i]["Status"] = "Completed";


                }

                //Completed逻辑：add或Edit页面手动设为completed的项目（算满12个月）
                if (dt.Rows[i]["savingCalculationStatus"].ToString() == "Completed")
                {
                    dt.Rows[i]["Status"] = "Completed";


                }

            }

            // 1. 先设置数据源
            gridControl1.DataSource = dt;

            // 2. 设置列和筛选条件
            gridView1.OptionsView.ShowIndicator = false;
            gridView1.Columns.Clear();
            // 在设置列宽前添加
            gridView1.OptionsView.ColumnAutoWidth = false;
            gridView1.Columns.AddVisible("PID", "No.").Width = 80;
            gridView1.Columns.AddVisible("origin", "Source").Width = 100;
            gridView1.Columns.AddVisible("type", "Type").Width = 100;
            gridView1.Columns.AddVisible("CIPLevel", "CIP level").Width = 80;
            gridView1.Columns.AddVisible("createDate", "Create date").Width = 90;
            gridView1.Columns.AddVisible("topic", "Topic").Width = 430; 
            gridView1.Columns.AddVisible("DepartmentName", "Resp. Dept.").Width = 120;
            gridView1.Columns.AddVisible("owner", "Owner").Width = 100;
            gridView1.Columns.AddVisible("IL", "IL").Width = 60;
            gridView1.Columns.AddVisible("IL6FirstDueDate", "Initial IL6 due date").Width = 90;
            gridView1.Columns.AddVisible("IL6CurrentDueDate", "IL6 due date").Width = 90;
            gridView1.Columns.AddVisible("IL6ActualDuedate", "IL6 actual date").Width = 90;
            gridView1.Columns.AddVisible("budgetSaving", "Planned saving kRMB").Width = 100;
            gridView1.Columns.AddVisible("actualSaving", "Actual saving kRMB").Width = 100;
            gridView1.Columns.AddVisible("Status", "Status").Width = 100;
            gridView1.Columns.AddVisible("comments", "Comments").Width = 200;

            gridView1.Columns["PID"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["origin"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["type"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["CIPLevel"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["createDate"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["topic"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["DepartmentName"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["owner"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["IL"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["IL6FirstDueDate"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["IL6CurrentDueDate"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["IL6ActualDuedate"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["budgetSaving"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["actualSaving"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["Status"].OptionsColumn.AllowEdit = false;
            gridView1.Columns["comments"].OptionsColumn.AllowEdit = false;

            Filter();
            //筛选条件

            //Toppic列左对齐
            gridView1.Columns["topic"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Near;


            // 禁止第3、4行被选中
            gridView1.CustomRowCellEdit += (sender, e) =>
            {
                if (e.RowHandle == 2 || e.RowHandle == 3)  // 因为行索引从0开始，所以第3、4行是2、3
                {
                    e.RepositoryItem.ReadOnly = true;
                }
            };

            // 禁止第3、4行被选中
            gridView1.CustomDrawRowIndicator += (sender, e) =>
            {
                if (e.RowHandle == 2 || e.RowHandle == 3)
                {
                    e.Appearance.BackColor = Color.LightGray;
                    e.Appearance.Options.UseBackColor = true;
                }
            };

            gridView1.OptionsSelection.EnableAppearanceFocusedRow = true;
            gridView1.FocusedRowChanged += (sender, e) =>
            {
                if (e.FocusedRowHandle == 2 || e.FocusedRowHandle == 3)
                {
                    gridView1.FocusedRowHandle = -1;
                }
            };

            // 4. 获取筛选后的数据并统计
            int completed = 0; int overdue = 0; int active = 0; int total = 0; int IL123 = 0; int IL4 = 0; int IL5 = 0; int IL6 = 0;

            // 获取筛选后的行数
            int filteredRowCount = gridView1.DataRowCount;

            // 遍历筛选后的行进行统计
            for (int i = 0; i < filteredRowCount; i++)
            {
                DataRow row = gridView1.GetDataRow(i);
                if (row != null)
                {
                    // 统计Status
                    switch (row["Status"].ToString())
                    {
                        case "Completed":
                            completed++;
                            break;
                        case "Overdue":
                            overdue++;
                            break;
                        case "Active":
                            active++;
                            break;
                    }

                    // 统计IL
                    switch (row["IL"].ToString())
                    {
                        case "IL1":
                        case "IL2":
                        case "IL3":
                            IL123++;
                            break;
                        case "IL4":
                            IL4++;
                            break;
                        case "IL5":
                            IL5++;
                            break;
                        case "IL6":
                            IL6++;
                            break;
                    }
                }
            }

            // 5. 计算总数并更新显示
            total = active + overdue + completed;
            lblCompletedNum.Text = completed.ToString();
            lblActiveNum.Text = active.ToString();
            lblOverdueNum.Text = overdue.ToString();
            lblTotalNum.Text = total.ToString();
            lblIL123Num.Text = IL123.ToString();
            lblIL4Num.Text = IL4.ToString();
            lblIL5Num.Text = IL5.ToString();
            lblIL6Num.Text = IL6.ToString();
            //统计Overdue rate

            decimal overdueRate = 0;

            if (total != 0)
            {
                // 将其中一个操作数转换为 decimal，确保小数除法
                overdueRate = (decimal)overdue / total;
            }

            string strOverdue = overdueRate.ToString("P0");
            lblOverdueRate.Text = "YTD" + " overdue rate:" + $"{strOverdue}";
        }

        public static int[] CalculateStatus(string department)
        {
            // 创建一个 DateTime 变量，值为去年 1 月 1 日
            DateTime currentDate = DateTime.Now;
            int lastYear = currentDate.Year - 1;
            DateTime lastYearJanuaryFirst = new DateTime(lastYear, 1, 1);
            //查询逻辑：A.工厂所有CIP L2、3   B.非ILX    C.上一年+今年所有项目
            string condition = $"DepartmentName = '{department}' and delTag=0 and (IL != 'ILX' OR IL IS NULL)  order by PID";//Department筛选的是部门名称
            DataTable dt = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_list", condition);
            //生成项目状态列
            dt.Columns.Add("Status", typeof(string));
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool isIL6CurrentDueDateNotNull = dt.Rows[i]["IL6CurrentDueDate"] != null && dt.Rows[i]["IL6CurrentDueDate"] != DBNull.Value;
                bool isIL6ActualDuedateNotNull = dt.Rows[i]["IL6ActualDuedate"] != null && dt.Rows[i]["IL6ActualDuedate"] != DBNull.Value;
                //1.先都默认为Active
                dt.Rows[i]["Status"] = "Active";

                //2.有计划开始日期，且没有实际开始日期，且计划开始日期早于今天的，视为Overdue
                if (isIL6CurrentDueDateNotNull && !isIL6ActualDuedateNotNull)
                {
                    DateTime il6CurrentDueDate = (DateTime)dt.Rows[i]["IL6CurrentDueDate"];
                    //不可以精确到时间,只能精确到日
                    il6CurrentDueDate = il6CurrentDueDate.Date;

                    if (il6CurrentDueDate < DateTime.Now.Date)
                    {
                        dt.Rows[i]["Status"] = "Overdue";
                    }
                }
                //3.覆盖Completed，add或Edit页面手动设为completed的项目（算满12个月）
                if (dt.Rows[i]["savingCalculationStatus"].ToString() == "Completed")
                {
                    dt.Rows[i]["Status"] = "Completed";


                }

                //Completed逻辑：add或Edit页面手动设为completed的项目（算满12个月）
                if (dt.Rows[i]["savingCalculationStatus"].ToString() == "Completed")
                {
                    dt.Rows[i]["Status"] = "Completed";


                }




                ////生成项目状态列（旧逻辑）
                //dt.Columns.Add("Status", typeof(string));
                //for (int i = 0; i < dt.Rows.Count; i++)
                //{
                //    bool isIL6CurrentDueDateNotNull = dt.Rows[i]["IL6CurrentDueDate"] != null && dt.Rows[i]["IL6CurrentDueDate"] != DBNull.Value;

                //    //Completed逻辑：add或Edit页面手动设为completed的项目（算满12个月）
                //    if (dt.Rows[i]["savingCalculationStatus"].ToString() == "Completed")
                //    {
                //        dt.Rows[i]["Status"] = "Completed";


                //    }
                //    //Overdue逻辑：只要 IL6CurrentDueDate 不为空，就获取该日期值，然后去判断 IL6ActualDuedate 是否为空，如果不为空就和 IL6ActualDuedate 比较，
                //    //如果为空就和当前日期比较，只要满足相应的大小关系就执行 overdue赋值
                //    //其他全为Active

                //    else if (isIL6CurrentDueDateNotNull)
                //    {
                //        DateTime il6CurrentDueDate = (DateTime)dt.Rows[i]["IL6CurrentDueDate"];
                //        //不可以精确到时间
                //        il6CurrentDueDate = il6CurrentDueDate.Date;
                //        bool isIL6ActualDuedateNotNull = dt.Rows[i]["IL6ActualDuedate"] != null && dt.Rows[i]["IL6ActualDuedate"] != DBNull.Value;
                //        DateTime compareDate = isIL6ActualDuedateNotNull ? (DateTime)dt.Rows[i]["IL6ActualDuedate"] : DateTime.Now;
                //        //不可以精确到时间
                //        compareDate = compareDate.Date;
                //        if (il6CurrentDueDate < compareDate)
                //        {
                //            dt.Rows[i]["Status"] = "Overdue";
                //        }
                //        else
                //        {
                //            dt.Rows[i]["Status"] = "Active";
                //        }
                //    }
                //    else
                //    {
                //        dt.Rows[i]["Status"] = "Active";
                //    }
            }


            int completed = 0; int overdue = 0; int active = 0;

          
           

            // 遍历筛选后的行进行统计
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                    // 统计Status
                    switch (dt.Rows[i]["Status"].ToString())
                    {
                        case "Completed":
                            completed++;
                            break;
                        case "Overdue":
                            overdue++;
                            break;
                        case "Active":
                            active++;
                            break;

                    }
            }

            // 返回数组，顺序为
            return new int[] { completed, active,overdue };
        }


        private void btnAdd_Click(object sender, EventArgs e)
        {
            CIPAdd.isEdit = false;
            Form form = new CIPAdd();
            form.FormClosing += CIPAdd_FormClosing;
            form.ShowDialog();
           
        }

        #region 统计筛选点击逻辑

        private void lblCompleted_Click(object sender, EventArgs e)
        {
            // 设置筛选条件（精确匹配）
            gridView1.Columns["Status"].FilterInfo =
                new ColumnFilterInfo("Status = 'Completed'");

            panel11.BackColor = Color.FromArgb(146, 208, 80);
            panel12.BackColor = Color.Transparent;
            panel13.BackColor = Color.Transparent;
            panel14.BackColor = Color.Transparent;
            panel15.BackColor = Color.Transparent;
            panel16.BackColor = Color.Transparent;
            panel17.BackColor = Color.Transparent;
            panel18.BackColor = Color.Transparent;
        }

        private void lblActive_Click(object sender, EventArgs e)
        {
            // 设置筛选条件（精确匹配）
            gridView1.Columns["Status"].FilterInfo =
                new ColumnFilterInfo("Status = 'Active'");

            panel11.BackColor = Color.Transparent;
            panel12.BackColor = Color.FromArgb(146, 208, 80);
            panel13.BackColor = Color.Transparent;
            panel14.BackColor = Color.Transparent;
            panel15.BackColor = Color.Transparent;
            panel16.BackColor = Color.Transparent;
            panel17.BackColor = Color.Transparent;
            panel18.BackColor = Color.Transparent;
           


        }

        private void lblOverDue_Click(object sender, EventArgs e)
        {
            // 设置筛选条件（精确匹配）
            gridView1.Columns["Status"].FilterInfo =
                new ColumnFilterInfo("Status = 'Overdue'");

            panel11.BackColor = Color.Transparent;
            panel12.BackColor = Color.Transparent;
            panel13.BackColor = Color.FromArgb(146, 208, 80);
            panel14.BackColor = Color.Transparent;
            panel15.BackColor = Color.Transparent;
            panel16.BackColor = Color.Transparent;
            panel17.BackColor = Color.Transparent;
            panel18.BackColor = Color.Transparent;

        }

        private void lblTotal_Click(object sender, EventArgs e)
        {
            // 完全清除Status列的筛选状态
            gridView1.ClearColumnsFilter();
            Filter();


            panel11.BackColor = Color.Transparent;
            panel12.BackColor = Color.Transparent;
            panel13.BackColor = Color.Transparent;
            panel14.BackColor = Color.FromArgb(146, 208, 80);
            panel15.BackColor = Color.Transparent;
            panel16.BackColor = Color.Transparent;
            panel17.BackColor = Color.Transparent;
            panel18.BackColor = Color.Transparent;
        }

        private void lblIL123_Click(object sender, EventArgs e)
        {
            // 设置筛选条件（精确匹配）
            gridView1.Columns["IL"].FilterInfo =
            new ColumnFilterInfo("[IL] IN ('IL1', 'IL2', 'IL3')");
            panel11.BackColor = Color.Transparent;
            panel12.BackColor = Color.Transparent;
            panel13.BackColor = Color.Transparent;
            panel14.BackColor = Color.Transparent;
            panel15.BackColor = Color.FromArgb(146, 208, 80);
            panel16.BackColor = Color.Transparent;
            panel17.BackColor = Color.Transparent;
            panel18.BackColor = Color.Transparent;
        }

        private void lblIL4_Click(object sender, EventArgs e)
        {

            // 设置筛选条件（精确匹配）
            gridView1.Columns["IL"].FilterInfo =
            new ColumnFilterInfo("[IL] ='IL4'");
            panel11.BackColor = Color.Transparent;
            panel12.BackColor = Color.Transparent;
            panel13.BackColor = Color.Transparent;
            panel14.BackColor = Color.Transparent;
            panel15.BackColor = Color.Transparent;
            panel16.BackColor = Color.FromArgb(146, 208, 80);
            panel17.BackColor = Color.Transparent;
            panel18.BackColor = Color.Transparent;
        }

        private void lblIL5_Click(object sender, EventArgs e)
        {
            // 设置筛选条件（精确匹配）
            gridView1.Columns["IL"].FilterInfo =
            new ColumnFilterInfo("[IL] ='IL5'");
            panel11.BackColor = Color.Transparent;
            panel12.BackColor = Color.Transparent;
            panel13.BackColor = Color.Transparent;
            panel14.BackColor = Color.Transparent;
            panel15.BackColor = Color.Transparent;
            panel16.BackColor = Color.Transparent;
            panel17.BackColor = Color.FromArgb(146, 208, 80);
            panel18.BackColor = Color.Transparent;
        }

        private void lblIL6_Click(object sender, EventArgs e)
        {
            // 设置筛选条件（精确匹配）
            gridView1.Columns["IL"].FilterInfo =
            new ColumnFilterInfo("[IL] ='IL6'");
            panel11.BackColor = Color.Transparent;
            panel12.BackColor = Color.Transparent;
            panel13.BackColor = Color.Transparent;
            panel14.BackColor = Color.Transparent;
            panel15.BackColor = Color.Transparent;
            panel16.BackColor = Color.Transparent;
            panel17.BackColor = Color.Transparent;
            panel18.BackColor = Color.FromArgb(146, 208, 80);
        }

        private void lblCompletedNum_Click(object sender, EventArgs e)
        {
            lblCompleted_Click(sender, e);
        }

        private void lblActiveNum_Click(object sender, EventArgs e)
        {
            lblActive_Click(sender,e);
        }

        private void lblOverdueNum_Click(object sender, EventArgs e)
        {
            lblOverDue_Click(sender, e);
        }

        private void lblTotalNum_Click(object sender, EventArgs e)
        {
            lblTotal_Click(sender,e);
        }

        private void lblIL123Num_Click(object sender, EventArgs e)
        {
            lblIL123_Click(sender, e);
        }

        private void lblIL4Num_Click(object sender, EventArgs e)
        {
            lblIL4_Click(sender, e);
        }

        private void lblIL5Num_Click(object sender, EventArgs e)
        {
            lblIL5_Click(sender, e);
        }
        private void lblIL6Num_Click(object sender, EventArgs e)
        {
            lblIL6_Click(sender, e);
        }


        #endregion

        private void gridView1_DoubleClick(object sender, EventArgs e)
        {
            GridView view = sender as GridView;
            if (view == null) return;

            // 获取当前聚焦行的第0列值
            object value = view.GetRowCellValue(view.FocusedRowHandle, view.Columns[0]);
            if (value == null) return;

            CIPAdd.isEdit = true;
            CIPAdd.SelectPID = Int32.Parse(value.ToString());
            projectReport.reportID = Int32.Parse(value.ToString());
            Form form = new CIPAdd();
            form.FormClosing += CIPAdd_FormClosing;
            form.ShowDialog();
        }

        private void gridView1_MouseUp(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                // 获取鼠标点击的行句柄
                GridView view = sender as GridView;
                int hitInfoRowHandle = view.CalcHitInfo(e.Location).RowHandle;

                if (hitInfoRowHandle >= 0)
                {
                    // 显示 PopupMenu
                    popupMenu1.ShowPopup(System.Windows.Forms.Control.MousePosition);
                }
            }
        }

        private void popupMenu1_ItemClick(object sender, ItemClickEventArgs e)
        {
            if (e.Item.Caption == "Edit")
            {
                // 获取当前选中行的ID
                GridView view = gridView1;
                int rowHandle = view.FocusedRowHandle;
                if (rowHandle >= 0)
                {
                    object value = view.GetRowCellValue(rowHandle, view.Columns[0]);
                    if (value != null)
                    {
                        CIPAdd.isEdit = true;
                        CIPAdd.SelectPID = Convert.ToInt32(value);
                        projectReport.reportID = Convert.ToInt32(value);
                        Form form = new CIPAdd();
                        form.FormClosing += CIPAdd_FormClosing;
                        form.ShowDialog();
                    }
                }
            }
            else if (e.Item.Caption == "Add")
            {
                btnAdd_Click(sender,  e);
            }
            else if (e.Item.Caption == "Project report")
            {
                // 获取当前选中行的ID
                GridView view = gridView1;
                int rowHandle = view.FocusedRowHandle;
                if (rowHandle >= 0)
                {
                    object value = view.GetRowCellValue(rowHandle, view.Columns[0]);
                    if (value != null)
                    {
                        projectReport.reportID = Convert.ToInt32(value);
                        Form form = new projectReport();
                        form.ShowDialog();
                    }
                }
            }
            else if (e.Item.Caption == "Saving calculation")
            {
                // 获取当前选中行的ID
                GridView view = gridView1;
                int rowHandle = view.FocusedRowHandle;
                if (rowHandle >= 0)
                {
                    object value = view.GetRowCellValue(rowHandle, view.Columns[0]);
                    if (value != null)
                    {
                        CostSavingLoader.calculateID = Convert.ToInt32(value);
                        CostSavingLoader.isCalculate = true;
                        CostSavingLoader.isOverview = false;
                        CIPAdd.SelectPID = Convert.ToInt32(value);
                        Form form = new CostSavingLoader();
                        form.ShowDialog();
                    }
                }
            }
            else if (e.Item.Caption == "MPP")
            {
                // 获取当前选中行的ID
                GridView view = gridView1;
                int rowHandle = view.FocusedRowHandle;
                if (rowHandle >= 0)
                {
                    object value = view.GetRowCellValue(rowHandle, view.Columns[0]);
                    if (value != null)
                    {
                        MPP.CurrentPID = Convert.ToInt32(value);
                        Form form = new MPP();
                        form.ShowDialog();
                    }
                }
            }

            else if (e.Item.Caption == "Delete")
            {
                barButtonItem2_Click(sender, e);
            }
        }

        private void CIPAdd_FormClosing(object sender, FormClosingEventArgs e)
        {
            //刷新列表数据
            InitGridView(selectdate);
            CalculateNewIL();
        }

        private void btnOverview_Click(object sender, EventArgs e)
        {
            CostSavingLoader.isOverview = true;
            CostSavingLoader.isOverviewwithSheets = true;

            Form form = new CostSavingLoader();
            form.ShowDialog();
        }
        private void Filter()
        {
            // 创建一个 DateTime 变量，值为去年 1 月 1 日
            DateTime currentDate = selectdate;
            int lastYear = currentDate.Year - 1;
            DateTime lastYearJanuaryFirst = new DateTime(lastYear, 1, 1);

            gridView1.Columns["createDate"].FilterInfo = new ColumnFilterInfo($"createDate >='{lastYearJanuaryFirst:yyyy/MM/dd}'");
            if (SystemInfo.readLevel == publicFunc.MaxDepartmentLevel())
            {
                //L4
                gridView1.Columns["CIPLevel"].FilterInfo = new ColumnFilterInfo("[CIPLevel] IN ('L2','L3')");

            }
            else
            {
                //L3
                gridView1.Columns["DepartmentName"].FilterInfo = new ColumnFilterInfo($"DepartmentName ='{SystemInfo.readparentDepName}'");
            }

        }


        private void CalculateNewIL()
        {

            //上个月
            DataTable dtlastmonth = GetLatestILDataByDepartment(-1,SystemInfo.readLevel == publicFunc.MaxDepartmentLevel(), selectdate);
            gridControlLastmonth.DataSource = dtlastmonth;
            //本月
            DataTable dtthismonth = GetLatestILDataByDepartment(0, SystemInfo.readLevel == publicFunc.MaxDepartmentLevel(), selectdate);
            gridControlThismonth.DataSource = dtthismonth;
            int il1 = 0; int il2 = 0; int il3 = 0; int il4 = 0; int il5 = 0; int il6 = 0; int total = 0; int il123 = 0;
            int IL1 = 0; int IL2 = 0; int IL3 = 0; int IL4 = 0; int IL5 = 0; int IL6 = 0; int TOTAL = 0; int IL123 = 0;
            int add1 = 0; int add2 = 0; int add3 = 0; int add4 = 0; int add5 = 0; int add6 = 0; int addTotal = 0; int add123 = 0;

            if (SystemInfo.readLevel == publicFunc.MaxDepartmentLevel())
            {

                //L4
                if (dtlastmonth.Rows.Count > 0)
                {
                    for (int i = 0; i < dtlastmonth.Rows.Count; i++)
                    {
                        il1 += Convert.ToInt32(dtlastmonth.Rows[i]["IL1"]);
                        il2 += Convert.ToInt32(dtlastmonth.Rows[i]["IL2"]);
                        il3 += Convert.ToInt32(dtlastmonth.Rows[i]["IL3"]);
                        il4 += Convert.ToInt32(dtlastmonth.Rows[i]["IL4"]);
                        il5 += Convert.ToInt32(dtlastmonth.Rows[i]["IL5"]);
                        il6 += Convert.ToInt32(dtlastmonth.Rows[i]["IL6"]);
                        il123 += Convert.ToInt32(dtlastmonth.Rows[i]["IL123"]);
                        total += Convert.ToInt32(dtlastmonth.Rows[i]["total"]);
                    }
                }
                if (dtthismonth.Rows.Count > 0)
                {
                    for (int i = 0; i < dtthismonth.Rows.Count; i++)
                    {
                        IL1 += Convert.ToInt32(dtthismonth.Rows[i]["IL1"]);
                        IL2 += Convert.ToInt32(dtthismonth.Rows[i]["IL2"]);
                        IL3 += Convert.ToInt32(dtthismonth.Rows[i]["IL3"]);
                        IL4 += Convert.ToInt32(dtthismonth.Rows[i]["IL4"]);
                        IL5 += Convert.ToInt32(dtthismonth.Rows[i]["IL5"]);
                        IL6 += Convert.ToInt32(dtthismonth.Rows[i]["IL6"]);
                        IL123 += Convert.ToInt32(dtthismonth.Rows[i]["IL123"]);
                        TOTAL += Convert.ToInt32(dtthismonth.Rows[i]["total"]);
                    }
                }


            }
            else
            {
                //L3
                if (dtlastmonth.Rows.Count > 0)
                {
                    DataRow[] matchingRows = dtlastmonth.Select($"department = '{SystemInfo.readparentDepName}'");
                    if (matchingRows.Length > 0)
                    {
                        DataRow dataRow = matchingRows[0];
                        il1 += Convert.ToInt32(dataRow["IL1"]);
                        il2 += Convert.ToInt32(dataRow["IL2"]);
                        il3 += Convert.ToInt32(dataRow["IL3"]);
                        il4 += Convert.ToInt32(dataRow["IL4"]);
                        il5 += Convert.ToInt32(dataRow["IL5"]);
                        il6 += Convert.ToInt32(dataRow["IL6"]);
                        il123 += Convert.ToInt32(dataRow["IL123"]);
                        total += Convert.ToInt32(dataRow["total"]);

                    }

                }
                if (dtthismonth.Rows.Count > 0)
                {
                    DataRow[] matchingRows = dtthismonth.Select($"department = '{SystemInfo.readparentDepName}'");
                    if (matchingRows.Length > 0)
                    {
                        DataRow dataRow = matchingRows[0];
                        IL1 += Convert.ToInt32(dataRow["IL1"]);
                        IL2 += Convert.ToInt32(dataRow["IL2"]); 
                        IL3 += Convert.ToInt32(dataRow["IL3"]);
                        IL4 += Convert.ToInt32(dataRow["IL4"]);
                        IL5 += Convert.ToInt32(dataRow["IL5"]);
                        IL6 += Convert.ToInt32(dataRow["IL6"]);
                        IL123 += Convert.ToInt32(dataRow["IL123"]);
                        TOTAL += Convert.ToInt32(dataRow["total"]);

                    }

                }
            }
            add1 = IL1 - il1;
            add2 = IL2 - il2;
            add3 = IL3 - il3;
            add4 = IL4 - il4;
            add5 = IL5 - il5;
            add6 = IL6 - il6;
            add123 = IL123 - il123;
            addTotal = TOTAL - total;
            lblAdd123.Text = add123.ToString();
            lblAdd4.Text = add4.ToString();
            lblAdd5.Text = add5.ToString();
            lblAdd6.Text = add6.ToString();
            lblLastmonth123.Text = il123.ToString();
            lblLastmonth4.Text = il4.ToString();
            lblLastmonth5.Text = il5.ToString();
            lblLastmonth6.Text = il6.ToString();
            //更改月份后当前月份数据覆盖更新
            lblIL123Num.Text = IL123.ToString();
            lblIL4Num.Text = IL4.ToString();
            lblIL5Num.Text = IL5.ToString();
            lblIL6Num.Text = IL6.ToString();



        }
        public static DataTable GetLatestILDataByDepartment(int monthAddIndex,bool isL4, DateTime referenceDate)
        {
            DataTable resultTable = new DataTable();
            string connectionString = SystemInfo.SQL;

            // 从连接字符串中提取数据库名称
            string databaseName = "";
            string[] connectionParts = connectionString.Split(';');
            foreach (string part in connectionParts)
            {
                if (part.Trim().StartsWith("Database=", StringComparison.OrdinalIgnoreCase))
                {
                    databaseName = part.Trim().Substring(9);
                    break;
                }
            }

            // 计算上个月第一天的日期
            DateTime lastMonthFirstDay = new DateTime(referenceDate.Year, referenceDate.Month, 1).AddMonths(monthAddIndex);
            string sqlQuery = "";

            if (isL4)
            {
                sqlQuery = $@"-- 先找到对应月份的最大批次号，然后返回该批次的所有记录
            WITH MaxBatch AS (
                SELECT MAX(batch) as max_batch
                FROM [{databaseName}].[dbo].[CIP_ILData]
                WHERE yearMonth = @LastMonthFirstDay and isOnlyCIP23=1
            )
            SELECT 
                ID,
                department,
                yearMonth,
                ILX,
                IL1,
                IL2,
                IL3,
                IL4,
                IL5,
                IL6,
                IL123,
                total,
                batch -- 添加batch列
            FROM [{databaseName}].[dbo].[CIP_ILData]
            WHERE yearMonth = @LastMonthFirstDay 
              and isOnlyCIP23=1
              and batch = (SELECT max_batch FROM MaxBatch);
    ";
            }
            else
            {
                sqlQuery = $@"-- 先找到对应月份的最大批次号，然后返回该批次的所有记录
            WITH MaxBatch AS (
                SELECT MAX(batch) as max_batch
                FROM [{databaseName}].[dbo].[CIP_ILData]
                WHERE yearMonth = @LastMonthFirstDay and isOnlyCIP23=0
            )
            SELECT 
                ID,
                department,
                yearMonth,
                ILX,
                IL1,
                IL2,
                IL3,
                IL4,
                IL5,
                IL6,
                IL123,
                total,
                batch -- 添加batch列
            FROM [{databaseName}].[dbo].[CIP_ILData]
            WHERE yearMonth = @LastMonthFirstDay 
              and isOnlyCIP23=0
              and batch = (SELECT max_batch FROM MaxBatch);
    ";
            }

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                    {
                        // 添加参数化查询以防止SQL注入并正确处理日期
                        command.Parameters.AddWithValue("@LastMonthFirstDay", lastMonthFirstDay);

                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(resultTable);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("查询失败: " + ex.Message);
                }
            }

            return resultTable;
        }

        private void barButtonItem2_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show("Are you sure you want to delete?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);

            if (result == DialogResult.Yes)
            {
                // 获取当前选中行的PID
                GridView view = gridView1;
                int rowHandle = view.FocusedRowHandle;
                if (rowHandle >= 0)
                {
                    object pidValue = view.GetRowCellValue(rowHandle, view.Columns[0]);
                    if (pidValue != null)
                    {
                        // 先通过PID获取ID
                        DataTable dt = LSQL.SQL.ReturnDataTable3(SystemInfo.SQL, "View_CIP_list", $"PID={pidValue} and delTag=0");
                        if (dt.Rows.Count > 0)
                        {
                            int cipID = Convert.ToInt32(dt.Rows[0]["ID"]); // 获取CIP_list表中的ID
                            string updateContent = "delTag=1";
                            
                            // 更新CIP_list表
                            string condition1 = $"[ID]={cipID} and delTag=0";
                            bool success = LSQL.SQL.Update(SystemInfo.SQL, "CIP_list", updateContent, condition1);

                            // 更新CIP_saving表
                            string condition2 = $"[CIPID]={cipID} and delTag=0";
                            success &= LSQL.SQL.Update(SystemInfo.SQL, "CIP_saving", updateContent, condition2);

                            // 更新CIP_SheetData表
                            success &= LSQL.SQL.Update(SystemInfo.SQL, "CIP_SheetData", updateContent, condition2);
                            //更新数据库IL统计数据
                            CIPAdd.UpdateILCounts(true);
                            CIPAdd.UpdateILCounts(false);
                            //更新MPP数据
                            string condition3 = $"[PID]={pidValue} and delTag=0";
                            success &= LSQL.SQL.Update(SystemInfo.SQL, "CIP_MPPTask", updateContent, condition3);
                            string condition4 = $"[PID]={pidValue}";
                            success &= LSQL.SQL.Update(SystemInfo.SQL, "CIP_MPPDependency", updateContent, condition4);





                            if (success)
                            {
                                MessageBox.Show("Deletion successful", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                InitGridView(selectdate); // 刷新列表
                                CalculateNewIL();//刷新IL数量

                            }
                            else
                            {
                                MessageBox.Show("Deletion failed", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            }
                        }
                        else
                        {
                            MessageBox.Show("Project not found", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        }
                    }
                }
            }
            else if (result == DialogResult.No)
            {
                MessageBox.Show("Deletion cancelled", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void btnSummary_Click(object sender, EventArgs e)
        {
            CostSavingLoader.isOverview = true;
            CostSavingLoader.isOverviewwithSheets = false;



            Form form = new CostSavingLoader();
            form.ShowDialog();

        }

        private void currentMonth_EditValueChanged(object sender, EventArgs e)
        {
            selectdate = currentMonth.DateTime;
            InitGridView(selectdate);
            CalculateNewIL();
        }

        private void CIPSFMView_KeyDown(object sender, KeyEventArgs e)
        {
            // 判断是否按下 F12 键
            if (e.KeyCode == Keys.F12)
            {
                gridControlLastmonth.Visible = !gridControlLastmonth.Visible;
                gridControlThismonth.Visible = !gridControlThismonth.Visible;
                e.Handled = true;
            }

        }
    }

}